<!DOCTYPE html>
<html lang="zh">
  <head>
    <meta charset="UTF-8" />
    <meta name="author" content="xmy6364">
    <meta name="description" content="想要成为大佬">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no" />

    <link rel="shortcut icon" href="/assets/favicon.ico" type="image/x-icon">
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@600&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/highlight.js/10.5.0/styles/default.min.css">
    <link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/highlight.js/10.5.0/styles/github-gist.min.css">
    <link rel="stylesheet" href="/assets/lib/css/font-awesome.min.css">
    <link rel="stylesheet" href="/assets/css/layout.css" />
    <script defer src="https://cdn.bootcdn.net/ajax/libs/clipboard.js/2.0.6/clipboard.min.js"></script>
    <script defer src="/assets/js/copyCode.js"></script>
    <script defer src="/assets/js/backTop.js"></script>
    <script defer src="/assets/js/tool.js"></script>

    
  <link rel="stylesheet" href="/assets/css/page.css" />
  <link rel="stylesheet" href="/assets/css/sidebar.css" />
  <link rel="stylesheet" href="/assets/css/footer.css" />
  <link rel="stylesheet" href="/assets/css/post.css" />
  <script defer src="/assets/js/backHome.js"></script>
  <script defer src="/assets/js/toc.js"></script>
  <script defer src="/assets/js/copyright.js"></script>


    <title>T-SQL学习笔记</title>
  </head>
  <body>
    <div class="container">
      <aside>
        
  <div class="sidebar">
  <header>梦的博客</header>
  <div class="info">
    <div class="avatar">
      <img src="https://cdn.jsdelivr.net/gh/xmy6364/blog-image/img/pixelartoc_1.png" alt="头像">
    </div>
    <div class="author">xmy6364</div>
    <div class="description">想要成为大佬</div>
    <div class="about">
      <a href="/about">about me.</a>
    </div>
  </div>
  <div class="links">
    <ul>
    
      <li class="links-item">
        <a href="https://github.com/xmy6364" target="_blank">
          <i class="fa fa-github-alt" aria-hidden="true"></i>
        </a>
      </li>
    
      <li class="links-item">
        <a href="tencent://message/?uin=1176281967" target="_blank">
          <i class="fa fa-qq" aria-hidden="true"></i>
        </a>
      </li>
    
    </ul>
  </div>
  <nav>
    <ul>
    
      <li class="nav-item">
        <a href="/archives">
          <span class="nav-item__count">33</span>
          <span class="nav-item__label">归档</span>
        </a>
      </li>
    
      <li class="nav-item">
        <a href="/categories">
          <span class="nav-item__count">2</span>
          <span class="nav-item__label">分类</span>
        </a>
      </li>
    
      <li class="nav-item">
        <a href="/tags">
          <span class="nav-item__count">45</span>
          <span class="nav-item__label">标签</span>
        </a>
      </li>
    
    </ul>
  </nav>
  <div class="catalogue" id="catalogue"></div>
</div>

      </aside>
      <main>
        
  <div class="post">
    <div class="post-front">
      <h1 class="post-front__title">T-SQL学习笔记</h1>
      <div class="post-front__desc">
        
        <p class="post-front__desc-date">
          <i class="fa fa-calendar" aria-hidden="true"></i>
          2019/09/09 20:47:19
        </p>
        
        
        <p class="post-front__desc-category">
          <i class="fa fa-folder-o" aria-hidden="true"></i>
          <a href="/categories/技术">
            技术
          </a>
        </p>
        
          <div class="post-front__desc-tags">
          
          <a href="/tags/T-SQL">
            <i class="fa fa-tag" aria-hidden="true"></i>
            T-SQL
          </a>
          
          <a href="/tags/SQL Server">
            <i class="fa fa-tag" aria-hidden="true"></i>
            SQL Server
          </a>
          
        </div>
      </div>
    </div>
    <div class="post-content">
      <nav id="toc" class="toc"><ol><li><a href="#if和while语句">if和while语句</a></li><li><a href="#自定义函数">自定义函数</a><ol><li><a href="#标量函数只返回一个基础类型数据的值">标量函数：只返回一个基础类型数据的值</a></li><li><a href="#表值函数返回一个table类型的结果集">表值函数：返回一个table类型的结果集</a><ol></ol></li><li><a href="#注意">注意</a></li></ol></li><li><a href="#存储过程">存储过程</a><ol><li><a href="#自定义存储过程">自定义存储过程</a></li><li><a href="#注意-1">注意:</a></li></ol></li><li><a href="#索引">索引</a></li><li><a href="#事务">事务</a></li><li><a href="#触发器">触发器</a></li></ol></nav><p>学习T-SQL时记录的笔记，记得并不全也不详细</p>
<h2 id="if和while语句">if和while语句</h2>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622203359962">复制</button><span class="hljs-keyword">declare</span> <span class="hljs-variable">@age</span> <span class="hljs-type">int</span>
<span class="hljs-keyword">select</span> <span class="hljs-variable">@age</span> <span class="hljs-operator">=</span> DATEDIFF(<span class="hljs-keyword">year</span>,stuAge,getdate()) <span class="hljs-keyword">from</span> TbStudent <span class="hljs-keyword">where</span> stuName <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;孙悟空&#x27;</span>

if(<span class="hljs-variable">@age</span><span class="hljs-operator">&gt;=</span><span class="hljs-number">18</span>)
<span class="hljs-keyword">begin</span> <span class="hljs-comment">--必须有begin..end</span>
	print N<span class="hljs-string">&#x27;已成年&#x27;</span>
<span class="hljs-keyword">end</span>
<span class="hljs-keyword">else</span>
<span class="hljs-keyword">begin</span>
	print N<span class="hljs-string">&#x27;未成年&#x27;</span>
<span class="hljs-keyword">end</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622203359962">declare @age int
select @age = DATEDIFF(year,stuAge,getdate()) from TbStudent where stuName = '孙悟空'

if(@age>=18)
begin --必须有begin..end
	print N'已成年'
end
else
begin
	print N'未成年'
end
</textarea>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622202889049">复制</button><span class="hljs-keyword">declare</span> <span class="hljs-variable">@sum</span> <span class="hljs-type">int</span>
<span class="hljs-keyword">set</span> <span class="hljs-variable">@sum</span> <span class="hljs-operator">=</span> <span class="hljs-number">0</span>
<span class="hljs-keyword">declare</span> <span class="hljs-variable">@i</span> <span class="hljs-type">int</span>
<span class="hljs-keyword">set</span> <span class="hljs-variable">@i</span> <span class="hljs-operator">=</span> <span class="hljs-number">1</span>
while(<span class="hljs-variable">@i</span> <span class="hljs-operator">&lt;=</span> <span class="hljs-number">100</span>)
<span class="hljs-keyword">begin</span>
	if(<span class="hljs-variable">@i</span> <span class="hljs-operator">%</span> <span class="hljs-number">2</span> <span class="hljs-operator">&lt;&gt;</span> <span class="hljs-number">0</span>)
	<span class="hljs-keyword">begin</span>
		<span class="hljs-keyword">set</span> <span class="hljs-variable">@sum</span> <span class="hljs-operator">=</span> <span class="hljs-variable">@sum</span> <span class="hljs-operator">+</span> <span class="hljs-variable">@i</span>
	<span class="hljs-keyword">end</span>
	<span class="hljs-keyword">set</span> <span class="hljs-variable">@i</span> <span class="hljs-operator">=</span> <span class="hljs-variable">@i</span> <span class="hljs-operator">+</span> <span class="hljs-number">1</span>
<span class="hljs-keyword">end</span>
print <span class="hljs-variable">@sum</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622202889049">declare @sum int
set @sum = 0
declare @i int
set @i = 1
while(@i <= 100)
begin
	if(@i % 2 <> 0)
	begin
		set @sum = @sum + @i
	end
	set @i = @i + 1
end
print @sum
</textarea>
<h2 id="自定义函数">自定义函数</h2>
<p>分为标量函数、表值函数（内联表值函数和多语句表值函数）</p>
<h3 id="标量函数只返回一个基础类型数据的值">标量函数：只返回一个基础类型数据的值</h3>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622198846015">复制</button><span class="hljs-comment">-- 语法</span>
<span class="hljs-keyword">create</span> <span class="hljs-keyword">function</span> 函数名
([参数列表])  可以不写参数,先写变量名再写类型
<span class="hljs-keyword">returns</span> 返回值类型
<span class="hljs-keyword">as</span>
<span class="hljs-keyword">begin</span>
<span class="hljs-comment">--	······函数体语句</span>
	<span class="hljs-keyword">return</span> 返回值
<span class="hljs-keyword">end</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622198846015">-- 语法
create function 函数名
([参数列表])  可以不写参数,先写变量名再写类型
returns 返回值类型
as
begin
--	······函数体语句
	return 返回值
end
</textarea>
<h3 id="表值函数返回一个table类型的结果集">表值函数：返回一个table类型的结果集</h3>
<h4>内联表值函数</h4>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622199854942">复制</button><span class="hljs-comment">-- 语法</span>
<span class="hljs-keyword">create</span> <span class="hljs-keyword">function</span> 函数名
([参数名])
<span class="hljs-keyword">returns</span> <span class="hljs-keyword">table</span>
<span class="hljs-keyword">as</span>
<span class="hljs-keyword">return</span> (一条<span class="hljs-keyword">select</span>语句)
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622199854942">-- 语法
create function 函数名
([参数名])
returns table
as
return (一条select语句)
</textarea>
<h4>多语句表值函数</h4>
<p>多语句表值函数可以看作是标量函数和内联表值函数的结合体</p>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622202280881">复制</button><span class="hljs-comment">-- 语法</span>
<span class="hljs-keyword">create</span> <span class="hljs-keyword">function</span> 函数名([参数列表])
<span class="hljs-keyword">returns</span> 表变量名 <span class="hljs-keyword">table</span>
(表变量的字段定义)
<span class="hljs-keyword">as</span>
<span class="hljs-keyword">begin</span>
	<span class="hljs-keyword">SQL</span>
	<span class="hljs-keyword">return</span> 这里啥都不写
<span class="hljs-keyword">end</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622202280881">-- 语法
create function 函数名([参数列表])
returns 表变量名 table
(表变量的字段定义)
as
begin
	SQL
	return 这里啥都不写
end
</textarea>
<h3 id="注意">注意</h3>
<ul>
<li>SQL自定义函数必须有返回值</li>
<li>在自定义函数中不允许修改基表内容（即，不能用insert,update,delete）</li>
<li>如果有多个参数，每个参数一之间用逗号隔开</li>
<li>调用函数时，函数名前要加dbo.</li>
</ul>
<h2 id="存储过程">存储过程</h2>
<p>存储过程时存储在服务器上的一组T-SQL语句的集合，用来完成一个特定功能。
分为系统存储过程(系统自带)和自定义存储过程</p>
<h3 id="自定义存储过程">自定义存储过程</h3>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622197742596">复制</button><span class="hljs-comment">-- 语法</span>
<span class="hljs-keyword">create</span> <span class="hljs-keyword">procedure</span>(或proc) 存储过程名(up_)
[参数列表] <span class="hljs-comment">--这里的参数列表不能使用圆括号</span>
<span class="hljs-keyword">begin</span>
	存储过程代码
<span class="hljs-keyword">end</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622197742596">-- 语法
create procedure(或proc) 存储过程名(up_)
[参数列表] --这里的参数列表不能使用圆括号
begin
	存储过程代码
end
</textarea>
<h3 id="注意-1">注意:</h3>
<ul>
<li>存储过程可以没有返回值</li>
<li>存储过程不适用return语句带回返回值，如果有返回值，直接使用select语句返回</li>
</ul>
<h2 id="索引">索引</h2>
<ul>
<li>索引：创建在表上</li>
<li>作用: 加快检索速度</li>
<li>全表扫面</li>
<li>索引分为聚集索引和非聚集索引</li>
<li>聚集索引: 在一个数据表中，只能创建一个聚集索引</li>
<li>主键会默认创建一个聚集索引</li>
<li>在你经常使用where的字段上添加非聚集索引</li>
<li>缺点：占用额外的存储空间，有可能降低insert、update、delete的速度</li>
</ul>
<h2 id="事务">事务</h2>
<p>事务时并发控制的单位，他是用户定义的一个操作，这些操作要么都做要么都不做，不可分割。
分为：SQL Server事务和ADO.NET事务</p>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622200756818">复制</button><span class="hljs-comment">-- 语法</span>
<span class="hljs-keyword">begin</span> tran  <span class="hljs-comment">--开始一个事务操作</span>
<span class="hljs-keyword">commit</span> tran <span class="hljs-comment">--提交</span>
<span class="hljs-keyword">rollback</span>    <span class="hljs-comment">--回滚</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622200756818">-- 语法
begin tran  --开始一个事务操作
commit tran --提交
rollback    --回滚
</textarea>
<p>C#中使用时，通常把事务的操作封装到存储过程中</p>
<h2 id="触发器">触发器</h2>
<ul>
<li>触发器是一种特殊的存储过程</li>
<li>只不过这个存储过程是不允许显示调用的</li>
<li>他只能在做了特定事件后，自动触发做出响应的</li>
<li>两张临时数据表：inserted、deleted</li>
<li>只能在触发器中访问</li>
<li>触发器是附着在一张表上的</li>
</ul>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622196017038">复制</button><span class="hljs-comment">-- 语法</span>
<span class="hljs-keyword">create</span> <span class="hljs-keyword">trigger</span> 触发器名字
<span class="hljs-keyword">on</span> 表名
after(或<span class="hljs-keyword">for</span>) 之后触发 <span class="hljs-operator">/</span> instead <span class="hljs-keyword">of</span> 之前触发 [<span class="hljs-keyword">insert</span><span class="hljs-operator">/</span><span class="hljs-keyword">delete</span><span class="hljs-operator">/</span>update]
<span class="hljs-keyword">as</span> 
<span class="hljs-keyword">begin</span>
<span class="hljs-comment">-- ······</span>
<span class="hljs-keyword">end</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622196017038">-- 语法
create trigger 触发器名字
on 表名
after(或for) 之后触发 / instead of 之前触发 [insert/delete/update]
as 
begin
-- ······
end
</textarea>
<p>触发器的触发条件：insert，delete，update</p>
<pre class="hljs"><code><button class="copy-btn" type="button" data-clipboard-action="copy" data-clipboard-target="#copy1622199417878">复制</button><span class="hljs-keyword">create</span> <span class="hljs-keyword">trigger</span> tr_Bank_insert
<span class="hljs-keyword">on</span> Bank
after <span class="hljs-keyword">insert</span>
<span class="hljs-keyword">as</span>
<span class="hljs-keyword">begin</span>
	print <span class="hljs-string">&#x27;往Bank表中插入了记录&#x27;</span>
<span class="hljs-keyword">end</span>

<span class="hljs-keyword">create</span> <span class="hljs-keyword">trigger</span> tr_Bank_Delete
<span class="hljs-keyword">on</span> Bank
after <span class="hljs-keyword">delete</span>
<span class="hljs-keyword">as</span>
<span class="hljs-keyword">begin</span>
	<span class="hljs-keyword">declare</span> <span class="hljs-variable">@id</span> <span class="hljs-type">int</span>
	<span class="hljs-keyword">declare</span> <span class="hljs-variable">@userName</span> nvarchar(<span class="hljs-number">8</span>)
	<span class="hljs-keyword">declare</span> <span class="hljs-variable">@userMoney</span> <span class="hljs-type">int</span>
	<span class="hljs-keyword">select</span> <span class="hljs-variable">@id</span> <span class="hljs-operator">=</span> id, <span class="hljs-variable">@userMoney</span> <span class="hljs-operator">=</span> userMoney, <span class="hljs-variable">@userName</span> <span class="hljs-operator">=</span> userName <span class="hljs-keyword">from</span> deleted
	<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> BankBak <span class="hljs-keyword">values</span>(<span class="hljs-variable">@id</span>, <span class="hljs-variable">@userName</span>, <span class="hljs-variable">@userMoney</span>)
<span class="hljs-keyword">end</span>

<span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> Bank
<span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> BankBak

<span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> Bank <span class="hljs-keyword">where</span> id <span class="hljs-operator">=</span> <span class="hljs-number">3</span>
<b class="name">SQL</b></code><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></pre><textarea style="position: absolute;top: -9999px;left: -9999px;z-index: -9999;" id="copy1622199417878">create trigger tr_Bank_insert
on Bank
after insert
as
begin
	print '往Bank表中插入了记录'
end

create trigger tr_Bank_Delete
on Bank
after delete
as
begin
	declare @id int
	declare @userName nvarchar(8)
	declare @userMoney int
	select @id = id, @userMoney = userMoney, @userName = userName from deleted
	insert into BankBak values(@id, @userName, @userMoney)
end

select * from Bank
select * from BankBak

delete from Bank where id = 3
</textarea>
<p>SQL Server的触发器是表级触发器，表上一次性的多次操作只触发一次</p>

    </div>
    
  </div>

        <footer>
        
  <div class="footer">
  
  <div class="theme">
    博客主题为 <a href="https://github.com/xmy6364/CoinRailgun">CoinRailgun</a> 默认主题
  </div>
  <div class="copyright">
    <span>© 2019-2021 xmy6364.</span>
    <span>Powered by <a href="https://github.com/xmy6364/CoinRailgun">CoinRailgun</a></span>
  </div>
</div>

        </footer>
      </main>
    </div>
  </body>
</html>
